﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using University.Workshop.Model;
using System.Globalization;

namespace University.Workshop.DataAccess
{
    public class PrincipalDataAccess: IDisposable
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=DataBaseHotel;Integrated Security=true";
        private SqlConnection cnn;

        public int GetClient(string roomName, DateTime date, string state)
        {
            RoomDataAccess roomdal = new RoomDataAccess();
            roomdal.Dispose();
            int idRoom = roomdal.GetIdRoomByName(roomName);
            cnn = new SqlConnection(ConnString);
            string sqlSentence="SELECT ";
            if (state == "Booked")
            {
                sqlSentence = "SELECT * From [DataBaseHotel].[dbo].[Booking]";
            }
            else
            {
                if (state == "Taken")
                {
                    sqlSentence = "SELECT * From [DataBaseHotel].[dbo].[TokenRooms]";
                }
            }
            sqlSentence += " WHERE idRoom=@id AND ";

            sqlSentence += " @Date  BETWEEN [CheckIn] AND [CheckOut] ";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@Date", date);
            cmd.Parameters.AddWithValue("@id", idRoom);
            try
            {
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    return Convert.ToInt32(reader["ClientCI"].ToString(),CultureInfo.InvariantCulture);
                }
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Booking A Room", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return 0;
        }
        public Information GetDates(string roomName, DateTime date, string state)
        {
            RoomDataAccess roomdal = new RoomDataAccess();
            roomdal.Dispose();
            int idRoom = roomdal.GetIdRoomByName(roomName);
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "SELECT ";
            if (state == "Booked")
            {
                sqlSentence = "SELECT * From [DataBaseHotel].[dbo].[Booking]";
            }
            else
            {
                if (state == "Taken")
                {
                    sqlSentence = "SELECT * From [DataBaseHotel].[dbo].[TokenRooms]";
                }
            }
            sqlSentence += " WHERE idRoom=@id AND ";

            sqlSentence += " @Date  BETWEEN [CheckIn] AND [CheckOut] ";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@Date", date);
            cmd.Parameters.AddWithValue("@id", idRoom);
            try
            {
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    // ROMER NO CAMBIES ESTA PARTE DEL CODIGO SE QUE DA WARNING PERO AL PONER EL CultureInfo.InvariantCulture NO FUNCA!!!! 
                    string sCheckIn = reader["CheckIn"].ToString();
                    DateTime nCheckIn = Convert.ToDateTime(sCheckIn);
                    DateTime nCheckOut = Convert.ToDateTime(reader["CheckOut"].ToString());
                    // ROMER NO CAMBIES ESTA PARTE DEL CODIGO SE QUE DA WARNING PERO AL PONER EL CultureInfo.InvariantCulture NO FUNCA!!!! 
                    return new Information { CheckIn = nCheckIn,  Checkout=nCheckOut};
                }
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Getting Dates", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return new Information();
        }

        #region Miembros de IDisposable

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (cnn != null)
                {
                    cnn.Dispose();
                    cnn = null;
                }
            }
        }

        #endregion

    }
}
